package com.ats.daoimpl;

import java.nio.channels.SelectableChannel;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import com.ats.dao.GetLeaveDao_ty;
import com.ats.model.GetRecord;
import com.ats.util.JDBCUtil;

public class GetLeaveDaoImpl_ty implements GetLeaveDao_ty{

	@Override
	public JSONObject getNoApproval(int iid,int page,int rows)	{
		JSONObject os = new JSONObject();
		JSONArray  as = new JSONArray();
		int total=0;
		StringBuffer  sql = new StringBuffer();
		sql.append(" SELECT  ats_leave.lid,ats_classe.cname,ats_student.`name`,ats_leave.lnumber,ats_leave.lreson,ats_leave.ldate");
		sql.append(" FROM ats_leave");
		sql.append(" INNER JOIN ats_classe_record  ");
		sql.append(" ON ats_leave.sid=ats.ats_classe_record.sid");
		sql.append(" INNER JOIN ats_classe");
		sql.append(" ON ats.ats_classe.cid=ats_classe_record.cid");
		sql.append(" INNER JOIN ats_student ");
		sql.append(" ON ats_student.sid=ats_leave.sid");
		sql.append(" WHERE ISNULL(ats_leave.lapprove) AND ats_leave.iid=?");
		sql.append(" LIMIT ?,?");
		Connection  con = JDBCUtil.getConnection();
		PreparedStatement pst;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1, iid);
			pst.setInt(2, (page-1)*rows);
			pst.setInt(3, rows);
			ResultSet  rs = pst.executeQuery();
			while(rs.next()){
				JSONObject j = new JSONObject();
				j.put("lid", rs.getString("lid")); 
				j.put("cname", rs.getString("cname"));
				j.put("sname",rs.getString("name"));
				j.put("lnumber",rs.getString("lnumber"));
				j.put("lreson",rs.getString("lreson"));
				j.put("ldate",rs.getString("ldate"));
				as.add(j);
				total++;
			}
			os.put("total", total);
			os.put("rows", as);
			JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return os;
	}

	@Override
	public boolean setleaver(int lid) {
		boolean flag=false;
		StringBuffer sql =new StringBuffer();
		sql.append(" UPDATE ats_leave");
		sql.append(" SET  lapprove='同意'");
		sql.append(" WHERE lid=?");
		Connection con =JDBCUtil.getConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1,lid);
			int rs =pst.executeUpdate();
			if(rs>0){
				flag=true;
			}
			JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}

	@Override
	public boolean setNoReson(int lid,String noreson) {
		boolean flag=false;
		StringBuffer sql =new StringBuffer();
		sql.append(" UPDATE  ats_leave");
		sql.append(" SET  lapprove='不同意',noreason=?");
		sql.append(" WHERE lid=?");
		Connection con =JDBCUtil.getConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, noreson);
			pst.setInt(2,lid);
			int rs =pst.executeUpdate();
			if(rs>0){
				flag=true;
			}
			JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag; 
	}

	@Override
	public JSONObject getStuLeave(int sid,int page,int rows) {
		JSONObject os = new JSONObject();
		JSONArray  as = new JSONArray();
		int total=0;
		StringBuffer  sql = new StringBuffer();
		sql.append(" SELECT ats_classe.cname,ats_student.`name`,ats_leave.lnumber,ats_leave.lreson,ats_leave.ldate,ats_leave.lapprove,ats_leave.noreason");
		sql.append(" FROM ats_leave");
		sql.append(" INNER JOIN ats_classe_record  ");
		sql.append(" ON ats_leave.sid=ats.ats_classe_record.sid  ");
		sql.append(" INNER JOIN ats_classe ");
		sql.append(" ON ats.ats_classe.cid=ats_classe_record.cid");
		sql.append(" INNER JOIN ats_student ");
		sql.append(" ON ats_student.sid=ats_leave.sid");
		sql.append(" WHERE ats_leave.iid=?");
		sql.append(" LIMIT ?,?");
		Connection  con = JDBCUtil.getConnection();
		PreparedStatement pst;
		try {			
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1, sid);
		    pst.setInt(2, (page-1)*rows);
			pst.setInt(3, rows);
			ResultSet  rs = pst.executeQuery();
			while(rs.next()){
				JSONObject j = new JSONObject();
				j.put("cname",rs.getString("cname"));
				j.put("sname",rs.getString("name"));
				j.put("lreson",rs.getString("lreson"));
				j.put("lnumber",rs.getString("lnumber"));
				j.put("noreason",rs.getString("noreason"));
				j.put("lapprove",rs.getString("lapprove"));
				j.put("ldate",rs.getString("ldate"));
				as.add(j);
				total++;
			}
			os.put("total", total);
			os.put("rows", as);
			JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return os;
	}

	@Override
	public JSONObject getTeacherLeave(int tid,int page,int rows) {
		JSONObject os=new JSONObject();
		JSONArray  as = new JSONArray();
		int total=0;
		StringBuffer  sql = new StringBuffer();
		sql.append(" SELECT ats_classe.cname,ats_student.`name`,ats_leave.lnumber,ats_leave.lreson,ats_leave.ldate,ats_leave.lapprove,ats_leave.noreason");
		sql.append(" FROM ats_leave");
		sql.append(" INNER JOIN ats_classe_record  ");
		sql.append(" ON ats_leave.sid=ats.ats_classe_record.sid  ");
		sql.append(" INNER JOIN ats_classe ");
		sql.append(" ON ats.ats_classe.cid=ats_classe_record.cid");
		sql.append(" INNER JOIN ats_student ");
		sql.append(" ON ats_student.sid=ats_leave.sid");
		sql.append(" WHERE ats_leave.iid=? AND ats_leave.lapprove='同意'");
		sql.append(" LIMIT ?,?");
		Connection  con = JDBCUtil.getConnection();
		PreparedStatement pst;
		try {			
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1, tid);
			pst.setInt(2, (page-1)*rows);
			pst.setInt(3, rows);
			ResultSet  rs = pst.executeQuery();
			while(rs.next()){
				JSONObject j = new JSONObject();
				j.put("cname",rs.getString("cname"));
				j.put("sname",rs.getString("name"));
				j.put("lnumber",rs.getString("lnumber"));
				j.put("lreson",rs.getString("lreson"));
				j.put("noreason",rs.getString("noreason"));
				j.put("ldate",rs.getString("ldate"));
				j.put("lapprove",rs.getString("lapprove"));
				as.add(j);
				total++;	
			}
			os.put("total", total);
			os.put("rows", as);
			JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return os;
	}

	@Override
	public JSONObject getInstructorLeave(int iid,int page,int rows) {
		JSONObject os = new JSONObject();
		JSONArray  as = new JSONArray();
		int total=0;
		StringBuffer  sql = new StringBuffer();
		sql.append(" SELECT ats_classe.cname,ats_student.`name`,ats_leave.lnumber,ats_leave.lreson,ats_leave.ldate,ats_leave.lapprove");
		sql.append(" FROM ats_leave");
		sql.append(" INNER JOIN ats_classe_record  ");
		sql.append(" ON ats_leave.sid=ats.ats_classe_record.sid  ");
		sql.append(" INNER JOIN ats_classe ");
		sql.append(" ON ats.ats_classe.cid=ats_classe_record.cid");
		sql.append(" INNER JOIN ats_student ");
		sql.append(" ON ats_student.sid=ats_leave.sid");
		sql.append(" WHERE ats_leave.iid=? AND ats_leave.lapprove='同意'");
		sql.append(" LIMIT ?,?");
		Connection  con = JDBCUtil.getConnection();
		PreparedStatement pst;
		try {			
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1, iid);
			pst.setInt(2, (page-1)*rows);
			pst.setInt(3, rows);
			ResultSet  rs = pst.executeQuery();
			while(rs.next()){
				JSONObject j = new JSONObject();
				j.put("cname",rs.getString("cname"));
				j.put("sname",rs.getString("name"));
				j.put("lnumber",rs.getString("lnumber"));
				j.put("lreson",rs.getString("lreson"));
				j.put("ldate",rs.getString("ldate"));
				j.put("lapprove",rs.getString("lapprove"));
				as.add(j);
				total++;
			}
			os.put("total", total);
			os.put("rows", as);
			JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return os;
	}
}